package top.rish.utils.excel;

import org.apache.poi.hssf.usermodel.HSSFDataFormatter;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import top.rish.utils.common.ExcelDateBuilder;
import top.rish.utils.common.Logger;
import top.rish.utils.common.StrUtil;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.*;


public class RishExcel {
    //日志
    private static final Logger LOGGER = Logger.getLogger();
    private int totalRows = 0;// 总行数
    private int totalCells = 0;// 总列数

    private String fileName;


    private Map<String, Map<String, String>> data = null ;

    /**
     * @param fileName excel文件的绝对路径
     */
    public RishExcel(String fileName) {
        this.fileName = fileName;
        read();
    }

    /**
     * @param file excel文件
     */
    public RishExcel(File file) {
        this.fileName = file.getAbsolutePath();
        read();
    }

    /**
     * @param inputStream excel文件流
     */
    public RishExcel(FileInputStream inputStream){
        read(inputStream);
    }

    /**
     * 从excel的各个单元格获取数据,创建bean
     * @param sheetName 标签页名称
     * @param startRow 开始行
     * @param endRow 结束行
     * @param clazz bean的类
     * @param <T>
     * @return
     * @throws Exception
     */
    public <T> T readBean(String sheetName,int startRow,int endRow,Class<T> clazz) throws Exception{
        return   ExcelDateBuilder.buildBean(read(sheetName,-1,startRow, endRow), startRow,endRow, clazz);
    }

    /**
     * 从excel的各个单元格获取数据,创建bean
     * @param startRow 数据开始的行
     * @param endRow 数据结束的行
     * @param clazz bean的类
     * @param <T>
     * @return
     * @throws Exception
     */
    public <T> T read( Class<T> clazz,int startRow, int endRow) throws Exception {
        return ExcelDateBuilder.buildBean(read(startRow, endRow), startRow,endRow, clazz);
    }

    /**
     * 从excel的指定行,创建 list
     * @param listStartRow 列表数据开始的行
     * @param clazz list元素bean的类
     * @param <T>
     * @return
     * @throws Exception
     */
    public <T> List<T> read(Class<T> clazz,int listStartRow) throws Exception {
        return ExcelDateBuilder.buildList(read(0), listStartRow,-1, clazz);
    }
    /**
     * 从excel的指定行,创建 list
     * @param sheetName 标签页名称
     * @param listStartRow 列表数据开始的行
     * @param clazz list元素bean的类
     * @param <T>
     * @return
     * @throws Exception
     */
    public <T> List<T> read(Class<T> clazz,String sheetName,int listStartRow) throws Exception {
        return ExcelDateBuilder.buildList(read(sheetName,-1,-1,-1), listStartRow,-1, clazz);
    }
    public <T> List<T> read(Class<T> clazz,String sheetName,int listStartRow,int listEndRow) throws Exception {
        return ExcelDateBuilder.buildList(read(sheetName,-1,-1,listEndRow), listStartRow,listEndRow, clazz);
    }


    /**
     * 从excel第一个标签页,读取n至m行的数据
     * @param startRow 开始的行
     * @param endRow 结束的行
     * @return Map<String, String> key['row,col','value']
     * @throws Exception
     */
    public Map<String, String> read(int startRow, int endRow) {
        return read(null, 0,startRow,endRow);
    }
    /**
     * 从excel第一个标签页,读取n行开始的数据
     * @param startRow 开始的行
     * @return Map<String, String> key['row,col','value']
     * @throws Exception
     */
    public Map<String, String> read(int startRow) {
        return  read(null, 0,startRow,-1);
    }
    /**
     * 从excel指定标签页,读取n至m行开始的数据
     * @param sheetName 标签页名称
     * @param startRow 开始的行
     * @param endRow 结束的行
     * @return Map<String, String> key['row,col','value']
     * @throws Exception
     */
    public Map<String, String> readSheet(String sheetName,int startRow,int endRow) {
        return read(sheetName,-1,startRow,endRow);
    }
    /**
     * 从excel指定第n个标签页,读取n至m行开始的数据
     * @param sheetIndex 标签页索引
     * @param startRow 开始的行
     * @param endRow 结束的行
     * @return Map<String, String> key['row,col','value']
     * @throws Exception
     */
    public Map<String, String> readSheet(int sheetIndex,int startRow,int endRow) {
        return read(null,sheetIndex,startRow,endRow);
    }
    /**
     * 从excel指定第n个标签页,读取所有行的数据
     * @param sheetIndex 标签页索引
     * @return Map<String, String> key['row,col','value']
     * @throws Exception
     */
    public Map<String, String> readSheet(int sheetIndex) {
        return read(null,sheetIndex,0,-1);
    }
    /**
     * 从excel指定标签页,读取所有行的数据
     * @param sheetName 标签页名称
     * @return Map<String, String> key['row,col','value']
     * @throws Exception
     */
    public Map<String, String> readSheet(String sheetName) {
        return read(sheetName,-1,-1,-1);
    }

    private static Map<String, String> readData(Map<String, String> dataMap,int startRow,int endRow){
        if(dataMap!=null&&dataMap.size()>0){
            boolean hasStart =   startRow > -1  ;
            boolean hasEnd =   endRow > -1  ;
            if ( hasStart || hasEnd ) {
                Map<String, String> result = new LinkedHashMap<>();
                for (Map.Entry<String, String> entry : dataMap.entrySet()) {
                    String key2 = entry.getKey();
                    String[] split = key2.split(",");
                    if(hasStart&&hasEnd){
                        if (Integer.valueOf(split[0]) >= startRow && Integer.valueOf(split[0]) <= endRow) {
                            result.put(key2, entry.getValue());
                        }
                    }else if(hasStart){
                        if (Integer.valueOf(split[0]) >= startRow ) {
                            result.put(key2, entry.getValue());
                        }
                    }else if(hasEnd){
                        if (Integer.valueOf(split[0]) <= endRow ) {
                            result.put(key2, entry.getValue());
                        }
                    }
                }
                return result;
            }else{
                return dataMap;
            }
        }
        return null;
    }
    private Map<String, String> read(String sheetName, int sheetIndex,int startRow,int endRow) {
        Map<String, Map<String, String>> read = read();
        if (read != null && read.size() > 0) {
            if (StrUtil.isNotBlank(sheetName)) {
                return readData(read.get(sheetName),startRow,endRow);
            }
            int i = 0;
            for (String key : read.keySet()) {
                if (i == sheetIndex) {
                   return readData(read.get(key),startRow,endRow);
                }
                i++;
            }
        }
        return null;
    }

    private Map<String, Map<String, String>> read(FileInputStream inputStream) {
        try {
            if (data == null) {
                Workbook wb = WorkbookFactory.create(inputStream);
                data = read(wb);
            }
        }  catch (IOException | InvalidFormatException e) {
            e.printStackTrace();
        }
        return data;
    }

    private Map<String, Map<String, String>> read() {
        if (this.fileName == null || !this.fileName.matches("^.+\\.(?i)((xls)|(xlsx))$")) {
            LOGGER.warn("excel文件有误!");
            return data;
        }
        File file = new File(this.fileName);
        if (file == null || !file.exists())
            return data;
        try {
            read(new FileInputStream(file));
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        return data;
    }


    private Map<String, Map<String, String>> read(Workbook wb) {
        data = new LinkedHashMap<>();
        int number = wb.getNumberOfSheets();
        if (number > 0) {
            for (int i = 0; i < number; i++) { // 循环每个工作表
                Map<String, String> sheetMap = new LinkedHashMap<>();
                int delnumber = 0;// 默认从第一行开始
                Sheet sheet = wb.getSheetAt(i);
                this.totalRows = sheet.getPhysicalNumberOfRows() - delnumber; // 获取工作表中行数
                if (this.totalRows >= 1 && sheet.getRow(delnumber) != null) {
                    for (int j = 0; j < totalRows; j++) {
                        this.totalCells = sheet.getRow(j)
                                .getPhysicalNumberOfCells(); // 得到当前行的所有单元格
                        for (int f = 0; f < sheet.getRow(j)
                                .getPhysicalNumberOfCells(); f++) {
                            if (totalCells > 0) {
                                String value = getCell(sheet.getRow(j).getCell(f));
                                sheetMap.put(j + "," + f, value);
                            }
                        }

                    }
                }
                data.put(sheet.getSheetName(), sheetMap);
            }
        }
        return data;
    }



    private String getCell(Cell cell) {
        String cellValue = null;
        HSSFDataFormatter hSSFDataFormatter = new HSSFDataFormatter();
        cellValue = hSSFDataFormatter.formatCellValue(cell); // 使用EXCEL原来格式的方式取得值
        return cellValue;
    }

    public static void main(String[] args) {
        try {
            Map<String, String> map = new RishExcel("C:\\Users\\25341\\Downloads\\导出.xlsx")
                    .readSheet(0,-1,1);
            System.out.println(map);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }


}